IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_UsersSelectForUserGroup]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_UsersSelectForUserGroup]
GO
create procedure [dbo].[cms_sp_UsersSelectForUserGroup]
@SiteGroupId int = null,
@SiteId int = null,
@UserGroupId int = null
as
select 
	Users.UserId as Id,
	Users.SiteGroupId,
	Users.SiteId,
	Users.LoginName,
	Users.Firstname,
	Users.Lastname,
	Users.IsEnabled,
	Users.IsSystem,
	Users.CreatedBy,
	Users.DateCreated,
	Users.LastUpdatedBy,
	Users.DateLastUpdated
from dbo.Users
inner join dbo.UserGroupLinks
	on UserGroupLinks.UserId = Users.UserId
where ((@UserGroupId is not null and (UserGroupLinks.UserGroupId = @UserGroupId)) or
	(@UserGroupId is null)) and
	Users.IsEnabled = 1 and
	((Users.SiteGroupId is null and Users.SiteId is null) or 
	(Users.SiteGroupId = @SiteGroupId and Users.SiteId is null) or
	(Users.SiteGroupId = @SiteGroupId and Users.SiteId = @SiteId))


GO

